*Import FNS data for eWIC analysis
*last modified: 19 March 2025
*last modified by: Kathya Tapia-Schythe

*-------------------------------------------------------------------------------
*--- Preamble
*-------------------------------------------------------------------------------

clear all
set more off
set rmsg on

set maxvar 120000
set emptycells drop

*-------------------------------------------------------------------------------
*--- Directories and Log 
*-------------------------------------------------------------------------------

local data_dir ./data/cleaned
local raw_dir ./data/raw
local out_dir ./analysis/output
local graph_dir ./analysis/output/graphs
local tab_dir ./analysis/output/tables
local log_dir ./documentation/logs
local date: display %tdYY-NN-DD date(c(current_date), "DMY")
di "`date'"
capture log close

log using `log_dir'/data_fns`date', replace

*-------------------------------------------------------------------------------
*--- WIC Agencies Monthly Costs - FNS
*-------------------------------------------------------------------------------

foreach y of numlist 2009/2025 {

 tempfile fns`y'
 import excel `raw_dir'/fns/WICagencies_2009_2025.xlsx, sheet("`y'") firstrow clear
 
 local y1=`y'-1
 
 if `y'==2025 {
 collapse (sum)fcosts_102024  (sum)fcosts_112024   ///
		  (sum)rebates_102024 (sum)rebates_112024, ///
		  by(agency)
 }
 
 else {
 collapse (sum)fcosts_10`y1'  (sum)fcosts_11`y1'  (sum)fcosts_12`y1'  ///
          (sum)fcosts_1`y'   (sum)fcosts_2`y'   (sum)fcosts_3`y'   ///
		  (sum)fcosts_4`y'   (sum)fcosts_5`y'   (sum)fcosts_6`y'   ///
		  (sum)fcosts_7`y'   (sum)fcosts_8`y'   (sum)fcosts_9`y'   ///
		  (sum)rebates_10`y1' (sum)rebates_11`y1' (sum)rebates_12`y1' ///
		  (sum)rebates_1`y'  (sum)rebates_2`y'  (sum)rebates_3`y'  ///
		  (sum)rebates_4`y'  (sum)rebates_5`y'  (sum)rebates_6`y'  ///
		  (sum)rebates_7`y'  (sum)rebates_8`y'  (sum)rebates_9`y', ///
		  by(agency)
 }

 reshape long fcosts_ rebates_, i(agency) j(time)
 rename fcosts_ fcosts
 rename rebates_ rebates
 
 save `fns`y'', replace 

}

clear 
foreach y of numlist 2009/2025 {
 append using `fns`y''
}

* Month-Year variable
tempvar str_time str_month str_year
tostring time, generate(`str_time')
gen `str_month'=substr(`str_time',1, length(`str_time')-4)
gen `str_year'=substr(`str_time',-4,.)
destring `str_month' `str_year', replace
drop time
gen time=ym(`str_year',`str_month')
format time %tm

* Fiscal Year
gen fiscalyear=.
foreach y of numlist 2009/2025 {
 local y0=`y'-1
 replace fiscalyear=`y' if inrange(time, ym(`y0',10), ym(`y',9))
}
tab fiscalyear, m

* State variable
tempvar st_code
split agency, parse(",") gen(code)
gen `st_code'=""                    
replace `st_code'=code2 if code3==""
replace `st_code'=code3 if code3!="" // 6,532 observations of agencies
replace `st_code' = subinstr(`st_code', " ", "",.) 
drop code* 
merge m:1 agency using `raw_dir'/fns/state_fips.dta, gen(merge_agency) keepusing(state)
replace state=`st_code' if state=="" & `st_code'!="" // 6,532 observations of agencies
merge m:1 state using `raw_dir'/fns/state_fips.dta, gen(merge_fips) keepusing(state_fips)
encode agency, generate(code_agency)
collapse (sum)fcosts (sum)rebates (mean)state_fips (mean)fiscalyear, by(state time)

* Define Panel
xtset state_fips time
xtdes

rename state_fips st_fips

lab data "Programs Costs WIC Agencies, FNS"
notes: Source https://www.fns.usda.gov/pd/wic-program
save `data_dir'/WICagencies_fns.dta, replace


log close
